﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using ExamSystem.Mdeol;
namespace ExamSystem.DAL
{
    public class TstudentDAL
    {
        //登录
        public Tstudent islogin(string no,string pwd)
        {
            Tstudent ts = null;
            string sql = "select * from tstudent where tsno='"+no+"' and tsPwd='"+pwd+"'";
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            if(dt.Rows.Count>0)
            {
                DataRow dr = dt.Rows[0];
                int tsid = (int)dr["tsid"];
                string tsno = dr["tsno"].ToString();
                string tsname = dr["tsname"].ToString();
                string tspwd = dr["tspwd"].ToString();
                string tssex = dr["tssex"].ToString();
                string tsphone = dr["tsphone"].ToString();
                ts = new Tstudent(tsid,null,tsno,tsname,tspwd,tssex,tsphone);

            }
            return ts;
        }






        //查询学生信息
       public List<Tstudent> selecttstudent(int classid,string stuname)
        {
            List<Tstudent> list = new List<Tstudent>();
            string sql = "select a.*,b.tcno from tstudent a,tclass b where a.tcid=b.tcid ";
           //0表示全部
           if(classid!=0)
           {
               sql += " and a.tcid="+classid;
           }
           if(!string.IsNullOrEmpty(stuname))
           {
               sql += " and a.tsname like '%"+stuname+"%'";
           }
           DataSet ds = DBhepter.GetDataSet(sql);
           DataTable dt = ds.Tables[0];
           for (int i = 0; i < dt.Rows.Count;i++ )
           {
               DataRow dr = dt.Rows[i];
               int tsId = int.Parse(dr["tsId"].ToString());
               int  tcId= int.Parse(dr["tcId"].ToString());
               string tcNo = dr["tcNo"].ToString();
               string tsNo=dr["tsNo"].ToString();
               string tsName=dr["tsName"].ToString();
               string tsPwd=dr["tsPwd"].ToString();
               string tsSex=dr["tsSex"].ToString();
               string tsPhone=dr["tsPhone"].ToString();
               Tclass tc = new Tclass(tcId,tcNo);
               Tstudent ts = new Tstudent(tsId,tc,tsNo,tsName,tsPwd,tsSex,tsPhone);
               list.Add(ts);
           }
           return list;
        }




       /// <summary>
       /// 根据id删除学生信息
       /// </summary>
       public int delete(int id)
       {
           string sql = "delete from tstudent where tsid=" + id;
           return DBhepter.ExecuteNonQuery(sql);
       }



       /// <summary>
       /// 根据id修改学生信息
       /// </summary>
       public int update(Tstudent t)
       {
           string sql = "update tstudent set tcId="+t.TcId.TcId+",tsNo='"+t.TsNo+"',tsName='"+t.TsName+"',tsPwd='"+t.TsPwd+"',tsSex='"+t.TsSex+"',tsPhone='"+t.TsPhone+"' where tsId=" + t.TsId;
           return DBhepter.ExecuteNonQuery(sql);
       }



       /// <summary>
       /// 根据id添加学生信息
       /// </summary>
       public int insert(Tstudent t)
       {
           int tcid = t.TcId.TcId;
           string sql = "insert into tstudent values("+tcid+",'"+t.TsNo+"','"+t.TsName+"','"+t.TsPwd+"','"+t.TsSex+"','"+t.TsPhone+"')";
           return DBhepter.ExecuteNonQuery(sql);
       }



        /// <summary>
        /// 根据根据学生学号查询学生信息
        /// </summary>
        public int selecttsno(string tsno)
       {
           string sql = "select * from tstudent where tsno='"+tsno+"'";
           DataSet ds= DBhepter.GetDataSet(sql);
           DataTable dt = ds.Tables[0];
           return dt.Rows.Count;
       }




        //查询学生信息
        public Tstudent selecttsid(int tsid)
        {
            Tstudent ts =null;
            string sql = "select a.*,b.tcno from tstudent a,tclass b where a.tcid=b.tcid and tsid=" + tsid;
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            if (dt.Rows.Count>0)
            {
                DataRow dr = dt.Rows[0];
                int tsId = int.Parse(dr["tsId"].ToString());
                int tcId = int.Parse(dr["tcId"].ToString());
                string tcNo = dr["tcNo"].ToString();
                string tsNo = dr["tsNo"].ToString();
                string tsName = dr["tsName"].ToString();
                string tsPwd = dr["tsPwd"].ToString();
                string tsSex = dr["tsSex"].ToString();
                string tsPhone = dr["tsPhone"].ToString();
                Tclass tc = new Tclass(tcId, tcNo);
                ts= new Tstudent(tsId, tc, tsNo, tsName, tsPwd, tsSex, tsPhone);
            }
            return ts;
        }







        //查询学生信息
        public List<Tstudent> selectall()
        {
            List<Tstudent> list = new List<Tstudent>();
            string sql = "select a.*,b.tcno from tstudent a,tclass b where a.tcid=b.tcid";
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            for (int i = 0; i < dt.Rows.Count;i++ )
            {
                DataRow dr = dt.Rows[i];
                int tsId = int.Parse(dr["tsId"].ToString());
                int tcId = int.Parse(dr["tcId"].ToString());
                string tcNo = dr["tcNo"].ToString();
                string tsNo = dr["tsNo"].ToString();
                string tsName = dr["tsName"].ToString();
                string tsPwd = dr["tsPwd"].ToString();
                string tsSex = dr["tsSex"].ToString();
                string tsPhone = dr["tsPhone"].ToString();
                Tclass tc = new Tclass(tcId, tcNo);
                Tstudent ts = new Tstudent(tsId, tc, tsNo, tsName, tsPwd, tsSex, tsPhone);
                list.Add(ts);
            }
            return list;
        }






        //查询学生信息
        public List<Tstudent> selectpager(int id)
        {
            List<Tstudent> list = new List<Tstudent>();
            string sql = "select a.*,b.tcno,(select SUM(tqscore) from tquestion where tqId in(select tqId from tanswer where tpId="+id+" and tsId=a.tsid and taAnswer=tqAnswer)) tsscore from tstudent a,tclass b where a.tcId=b.tcId and a.tsId in(select tsId from tpagerstudent where tpId="+id+")";
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                int tsId = int.Parse(dr["tsId"].ToString());
                int tcId = int.Parse(dr["tcId"].ToString());
                string tcNo = dr["tcNo"].ToString();
                string tsNo = dr["tsNo"].ToString();
                string tsName = dr["tsName"].ToString();
                string tsPwd = dr["tsPwd"].ToString();
                string tsSex = dr["tsSex"].ToString();
                string tsPhone = dr["tsPhone"].ToString();
                int tsscore = dr["tsscore"].ToString() != "" ? (int)dr["tsscore"] : 0;
                Tclass tc = new Tclass(tcId, tcNo);
                Tstudent ts = new Tstudent(tsId, tc, tsNo, tsName, tsPwd, tsSex, tsPhone,tsscore);
                list.Add(ts);
            }
            return list;
        }



        //查询学生信息
        public List<Tstudent> select()
        {
            List<Tstudent> list = new List<Tstudent>();
            string sql = "select a.*,b.tcno from tstudent a,tclass b where a.tcid=b.tcid";
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                int tsId = int.Parse(dr["tsId"].ToString());
                int tcId = int.Parse(dr["tcId"].ToString());
                string tcNo = dr["tcNo"].ToString();
                string tsNo = dr["tsNo"].ToString();
                string tsName = dr["tsName"].ToString();
                string tsPwd = dr["tsPwd"].ToString();
                string tsSex = dr["tsSex"].ToString();
                string tsPhone = dr["tsPhone"].ToString();
                Tclass tc = new Tclass(tcId, tcNo);
                Tstudent ts = new Tstudent(tsId, tc, tsNo, tsName, tsPwd, tsSex, tsPhone);
                list.Add(ts);
            }
            return list;
        }
    }
}